﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;

namespace DAO
{
    public class SQLData
    {
        private String _strMyconnecString = @"Data Source=QUANGPHUC\QUANGPHUC;Initial Catalog=cms;User ID=sa;Password=hlg123";
        private SqlCommand _sqlCommand;
        private SqlConnection _sqlConnec;
        private String _strErr;        
        private const int _time = 30;
        private String _strMaHoa = String.Empty;
        public SQLData() { }
        public SQLData(String myconnecString) {
             _strMyconnecString = myconnecString;
        }

        public String MyConnecString
        {
            get
            {
                return this._strMyconnecString;
            }
            set
            {
                this._strMyconnecString = value;
            }
        }

        public SqlConnection Connection {
            get { return new SqlConnection(_strMyconnecString); }
        }

        public String Restore(String pathFile,String databaseName) {
            String str = "Restore database successfully";
            try
            {
                this.Open();
                _sqlCommand = new SqlCommand(String.Format("RESTORE database {0} FROM DISK='{1}'", databaseName, pathFile), this._sqlConnec);
                _sqlCommand.ExecuteNonQuery();
                this.Close();
            }
            catch (Exception error)
            {
                str = "Error Occured During DB restore process !</br>" + error.Message;
            }
            return str;

        }
        public String Backup(String pathFile, String databaseName)
        {
            String str = "Backup database successfully";
            try
            {
                this.Open();
                _sqlCommand = new SqlCommand(String.Format("backup database {0} to disk='{1}'", this._sqlConnec.Database, pathFile), this._sqlConnec);
                _sqlCommand.ExecuteNonQuery();
                this.Close();
            }
            catch (Exception error)
            {
                str = "Error Occured During DB backup process !</br>" + error.Message;
            }
            return str;
        }

        public String Err
        {
            get
            {
                return this._strErr;
            }
            set
            {
                this._strErr = value;
            }
        }

        public void Open()
        {
            try
            {

                _sqlConnec = new SqlConnection(_strMyconnecString);
                _sqlConnec.Open();
            }
            catch (Exception err)
            {
                this._strErr = err.Message.ToString();
            }
        }

        public void Close()
        {
            if (_sqlConnec.State == ConnectionState.Open)
            {
                _sqlConnec.Close();
            }
        }

        public Boolean CreateProc(String sSql) {
            Boolean bBool = false;
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = sSql; 
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = CommandType.Text;                 
                _sqlCommand.ExecuteNonQuery();
                bBool = true;
            }
            catch (Exception err)
            {
                this._strErr = err.Message.ToString();
                bBool = false;
                
            }
            Close();
            return bBool;
        }
        public Boolean ExecuteNonQuery(String parameter, Object value, String commandText, CommandType commandType)
        {
            Boolean bBool = false;
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = commandText;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = commandType;
                _sqlCommand.Parameters.AddWithValue("@" + parameter.Trim(), value);
                _sqlCommand.ExecuteNonQuery();
                bBool = true;

            }
            catch (Exception err)
            {
                this._strErr = err.Message.ToString();
                bBool = false;
            }
            Close();
            return bBool;
        }

        public int ExecuteNonQuery(String strSQL, CommandType commandType, params SqlParameter[] arrParameter)
        {
            int id = -1;
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = strSQL;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = commandType;
                _sqlCommand.Parameters.AddRange(arrParameter);
                id=_sqlCommand.ExecuteNonQuery();
                
            }
            catch
            {
            }
            Close();
            return id;
        }

        public int ExecuteNonQuery(String strSQL, CommandType commandType, SqlParameter outParam,
            ref Object obj, params SqlParameter[] arrParameter)
        {
            int id = -1;
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = strSQL;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = commandType;
                _sqlCommand.Parameters.Add(outParam);
                _sqlCommand.Parameters.AddRange(arrParameter);
                id = _sqlCommand.ExecuteNonQuery();
                obj = _sqlCommand.Parameters[outParam.ParameterName].Value;
            }
            catch
            {
            }
            Close();
            return id;
        }

        public int ExecuteNonQuery(String strSQL, params SqlParameter[] arrParameter)
        {
            int id = -1;
            try
            {
                
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = strSQL;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = CommandType.StoredProcedure;
                _sqlCommand.Parameters.AddRange(arrParameter);
                id = _sqlCommand.ExecuteNonQuery();

            }
            catch
            {

            }
            
            return id;
        }

        public DataSet GetDataSet(String mySql) {
            DataSet dtSet = new DataSet();
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = mySql;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = CommandType.Text;
                using (SqlDataAdapter da = new SqlDataAdapter(_sqlCommand))
                {
                    da.Fill(dtSet);                    
                    da.Dispose();
                }
            }
            catch { }
            Close();
            return dtSet;
        }
        public DataTable GetTable(String strSQL)
        {
            DataTable tbl = new DataTable();
            try
            {
                Open();
                _sqlCommand = new SqlCommand();
                _sqlCommand.CommandText = strSQL;
                _sqlCommand.Connection = _sqlConnec;
                _sqlCommand.CommandType = CommandType.Text;
                using (SqlDataAdapter da = new SqlDataAdapter(_sqlCommand))
                {
                    //da.Fill(tbl);
                    da.FillSchema(tbl, SchemaType.Source);
                    da.Dispose();
                }
            }
            catch { }
            Close();
            return tbl;
        }
    }
}
